package cn.com.libertymutual.sp.dao;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.com.libertymutual.sp.bean.TbSpInScoreLog;
import cn.com.libertymutual.sp.req.ScoreDetail;
@Repository
public interface InScoreLogDao extends PagingAndSortingRepository<TbSpInScoreLog, Integer>, JpaSpecificationExecutor<TbSpInScoreLog>{

	TbSpInScoreLog findByUserCodeAndStatus(String userCode, String status);

	@Query(" from TbSpInScoreLog t where t.reasonNo=?1 and t.userCode = ?2 and t.telephone=?3 and t.status =1")
	List<TbSpInScoreLog> findInScoreHis(String policyNo, String userCode, String mobile);


	/**
	 * 查询过期积分
	 * @return
	 */
	@Query("from TbSpInScoreLog t where balance<acChangeScore and invalidDate<=sysdate() and status='1'")
	List<TbSpInScoreLog> findInvalidate();
	/**
	 * 更新过期积分
	 * @return
	 */
	@Transactional
	@Modifying
	@Query("update TbSpInScoreLog t set status='0' where  balance<acChangeScore and invalidDate<=sysdate() and status='1'")
	List<TbSpInScoreLog> updateInvalidate();

	/**
	 * 查询将生效积分
	 * @return
	 */
	@Query("from TbSpInScoreLog t where acChangeScore=0 and effictiveDate<=sysdate() and status='1'")
	List<TbSpInScoreLog> findEffictive();
	/**
	 * 更新生效积分
	 * @return
	 */
	@Transactional
	@Modifying
	@Query("update TbSpInScoreLog t  set acChangeScore=reChangeScore,balance=reChangeScore where acChangeScore=0 and effictiveDate<=sysdate() and status='1' ")
	public int updateEffictive();
	

//	@Query("select t from TbSpInScoreLog t where t.userCode = ?1 and t.effictiveDate >?3 and t.status =1")
//	List<TbSpInScoreLog> findByUCodeAndDate(String userCode, String date);

	@Query("select t.acChangeScore as score,t.effictiveDate as date from TbSpInScoreLog t where t.userCode = ?1 and t.effictiveDate >?2 and t.status =1")
	List<String> findByUCodeAndDate(String userCode, Date date);
	
	@Query("select COALESCE(sum(t.acChangeScore),0) from TbSpInScoreLog t where t.userCode = ?1 and t.effictiveDate >?2 and t.status =1")
	int findSumScoreByUCodeAndDate(String userCode, Date date);

	@Query(value="select * from (select t1.change_type,t1.re_changescore,t1.ac_changescore,t1.reason,t1.reason_no,t1.change_time,t1.remark from tb_sp_inscorelog as t1 where t1.user_code = :userCode ) q union  " + 
			"select * from (select t2.change_type,t2.re_changescore,t2.ac_changescore,t2.reason,t2.reason_no,t2.change_time,t2.remark from tb_sp_outscorelog as t2 where t2.user_code = :userCode ) p limit :pageNumber , :pageSize",nativeQuery=true)
	List<String> findAllInAndOut(@Param("userCode") String userCode,
			@Param("pageNumber") int pageNumber, @Param("pageSize") int pageSize);

//	@Query(value="select * from (select t1.change_type as changeType,t1.re_changescore as reChangeScore,t1.ac_changescore as acChangeScore,t1.reason as reason,t1.reason_no as reasonNo,t1.change_time as changeTime,t1.remark as remark from tb_sp_inscorelog as t1 where t1.user_code = :userCode) q union  \r\n" + 
//			"select * from (select t2.change_type as changeType,t2.re_changescore as reChangeScore,t2.ac_changescore as acChangeScore,t2.reason as reason,t2.reason_no as reasonNo,t2.change_time as changeTime,t2.remark as remark  from tb_sp_outscorelog as t2 where t2.user_code = :userCode ) p  limit :pageNumber , :pageSize",nativeQuery=true)
//	List<Object[]> findAllInAndOut(@Param("userCode") String userCode,
//			@Param("pageNumber") int pageNumber, @Param("pageSize") int pageSize);
	
//	@Query("select new cn.com.libertymutual.sp.req.ScoreDetail from (select t1.changeType as changeType,t1.reChangeScore as reChangeScore,t1.acChangeScore as acChangeScore,t1.reason as reason,t1.reasonNo as reasonNo,t1.changeTime as changeTime,t1.remark as remark from TbSpInScoreLog as t1 where t1.userCode = :userCode) q union  " + 
//			"select new cn.com.libertymutual.sp.req.ScoreDetail from (select t2.changeType as changeType,t2.reChangeScore as reChangeScore,t2.acChangeScore as acChangeScore,t2.reason as reason,t2.reasonNo as reasonNo,t2.changeTime as changeTime,t2.remark as remark  from TbSpOutScoreLog as t2 where t2.userCode = :userCode ) p  limit :pageNumber , :pageSize")
//	List<ScoreDetail> findAllInAndOut(@Param("userCode") String userCode,
//			@Param("pageNumber") int pageNumber, @Param("pageSize") int pageSize);
//	@Query("select new cn.com.libertymutual.sp.req.ScoreDetail(q.changeType,q.reChangeScore,q.acChangeScore,q.reason,q.reasonNo,q.changeTime,q.remark) from TbSpInScoreLog q where q.userCode = :userCode union  " + 
//			"select new cn.com.libertymutual.sp.req.ScoreDetail(p.changeType,p.reChangeScore,p.acChangeScore,p.reason,p.reasonNo,p.changeTime,p.remark) from TbSpOutScoreLog p where p.userCode = :userCode  limit :pageNumber , :pageSize")
//	List<ScoreDetail> findAllInAndOut(@Param("userCode") String userCode,
//			@Param("pageNumber") int pageNumber, @Param("pageSize") int pageSize);
//	@Query("select new cn.com.libertymutual.sp.req.ScoreDetail(q.changeType,q.reChangeScore,q.acChangeScore,q.reason,q.reasonNo,q.changeTime,q.remark) from TbSpInScoreLog q where q.userCode = :userCode ")
//	List<ScoreDetail> findAllInAndOut(@Param("userCode") String userCode);
	
	@Query(value="select t1.change_type,t1.re_changescore,t1.ac_changescore,t1.reason,t1.reason_no,t1.change_time,t1.remark from tb_sp_inscorelog as t1 where t1.user_code = :userCode limit :pageNumber , :pageSize",nativeQuery=true)
	List<String> findAllIn(String userCode, int pageNumber, int pageSize);

}
